package com.navi.mould1.service.impl;

import com.mysql.jdbc.Util;
import com.navi.core.context.HistoryContext;
import com.navi.core.mysqlEntity.BisDataEntity;
import com.navi.core.repository.NaviRepository;
import com.navi.core.repository.impl.MySqlRepositoryImpl;
import com.navi.core.util.JacksonUtil;
import com.navi.mould1.service.IBasicDataService;
import com.navi.mould1.tx.xpbisdata.XpbisdataI;
import com.navi.mould1.tx.xpbisdata.XpbisdataO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.util.List;
import java.util.UUID;
import static com.navi.core.constant.GenericDef.*;
import static com.navi.core.util.ExcelUtil.getCellValue;


@Slf4j
@Service
@Transactional
public class BasicDataServiceImpl implements IBasicDataService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    QueryRunner qr = new QueryRunner();

    @Autowired
    private NaviRepository dao;

    @Autowired
    private MySqlRepositoryImpl mySqlRepository;

    @Override
    public String subMainProc(String strInObj) {
        HistoryContext.clear(this.getClass());

        XpbisdataI inTrx = new XpbisdataI();
        XpbisdataO outTrx = new XpbisdataO();

        inTrx = JacksonUtil.fromJson(strInObj, XpbisdataI.class);
        logger.info(strInObj);

        String actionFlg = inTrx.getAction_flg();
        switch (actionFlg) {
            case "Q":
                outTrx = findProc(inTrx);
                break;
            case "A":
                outTrx = addProc(inTrx);
                break;
            case "D":
                outTrx = deleteProc(inTrx);
                break;
            case "U":
                outTrx = updateProc(inTrx);
                break;
            default:
                break;
        }

        final String strOutTrx = JacksonUtil.toJSONStr(outTrx);
        return strOutTrx;
    }


    private XpbisdataO findProc(XpbisdataI inTrx) {
        final BisDataEntity iary = inTrx.getIary().get(0);
        String orderByColumn =  inTrx.getOrderByColumn();

        StringBuffer hqlBuffer = new StringBuffer("From BisDataEntity where 1=1");
        if (StringUtils.isNotEmpty(iary.getDataSeqId())) {
            hqlBuffer.append(" and dataSeqId = '").append(iary.getDataSeqId()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getUnqSeqId())) {
            hqlBuffer.append(" and unqSeqId = '").append(iary.getUnqSeqId()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getDataCate())) {
            hqlBuffer.append(" and dataCate = '").append(iary.getDataCate()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getDataId())) {
            hqlBuffer.append(" and dataId = '").append(iary.getDataId()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getDataExt())) {
            hqlBuffer.append(" and dataExt = '").append(iary.getDataExt()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getDataItem())) {
            hqlBuffer.append(" and dataItem = '").append(iary.getDataItem()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt1())) {
            hqlBuffer.append(" and ext1 = '").append(iary.getExt1()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt2())) {
            hqlBuffer.append(" and ext2 = '").append(iary.getExt2()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt3())) {
            hqlBuffer.append(" and ext3 = '").append(iary.getExt3()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt4())) {
            hqlBuffer.append(" and ext4 = '").append(iary.getExt4()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt5())) {
            hqlBuffer.append(" and ext5 = '").append(iary.getExt5()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt6())) {
            hqlBuffer.append(" and ext6 = '").append(iary.getExt6()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt7())) {
            hqlBuffer.append(" and ext7 = '").append(iary.getExt7()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt8())) {
            hqlBuffer.append(" and ext8 = '").append(iary.getExt8()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt9())) {
            hqlBuffer.append(" and ext9 = '").append(iary.getExt9()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getExt10())) {
            hqlBuffer.append(" and ext10 = '").append(iary.getExt10()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getDataDesc())) {
            hqlBuffer.append(" and dataDesc = '").append(iary.getDataDesc()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getEvtUsr())) {
            hqlBuffer.append(" and evtUsr = '").append(iary.getEvtUsr()).append("'");
        }
        if (iary.getEvtTimestamp() != null) {
            hqlBuffer.append(" and evtTimestamp = '").append(iary.getEvtTimestamp()).append("'");
        }
        if (StringUtils.isNotEmpty(iary.getMailFlg())) {
            hqlBuffer.append(" and mailFlg = '").append(iary.getMailFlg()).append("'");
        }
        hqlBuffer.append(" ORDER BY " + orderByColumn);

        final List<BisDataEntity> resultDatas = dao.find(BisDataEntity.class,hqlBuffer.toString());
        XpbisdataO outTrx = new XpbisdataO();
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        outTrx.setOary(resultDatas);
        outTrx.setTbl_cnt(resultDatas.size());
        return outTrx;
    }


    @Transactional
    public XpbisdataO addProc(XpbisdataI inTrx) {
        List<BisDataEntity> iaryList = inTrx.getIary();
        XpbisdataO outTrx = new XpbisdataO();

        try {
            for (BisDataEntity iary : iaryList) {
                BisDataEntity bis_data = new BisDataEntity();
                if (iary.getDataCate().isEmpty()) {
                    outTrx.setRtn_code(E_BIS_DATA + E_ADD_EXIST + "");
                    outTrx.setRtn_mesg("参数基本信息不能为空");
                    return outTrx;
                }
                bis_data.setDataSeqId(UUID.randomUUID().toString().replace("-", "").substring(0, 25));
                bis_data.setDataCate(iary.getDataCate());
                if (StringUtils.isNotEmpty(iary.getUnqSeqId())){
                    bis_data.setUnqSeqId(iary.getUnqSeqId());
                }
                if (StringUtils.isNotEmpty(iary.getDataId())){
                    bis_data.setDataId(iary.getDataId());
                }
                if (StringUtils.isNotEmpty(iary.getDataExt())){
                    bis_data.setDataExt(iary.getDataExt());
                }
                if (StringUtils.isNotEmpty(iary.getDataItem())){
                    bis_data.setDataItem(iary.getDataItem());
                }
                if (StringUtils.isNotEmpty(iary.getDataDesc())){
                    bis_data.setDataDesc(iary.getDataDesc());
                }
                if (StringUtils.isNotEmpty(iary.getExt1())){
                    bis_data.setExt1(iary.getExt1());
                }
                if (StringUtils.isNotEmpty(iary.getExt2())){
                    bis_data.setExt2(iary.getExt2());
                }
                if (StringUtils.isNotEmpty(iary.getExt3())){
                    bis_data.setExt3(iary.getExt3());
                }
                if (StringUtils.isNotEmpty(iary.getExt4())){
                    bis_data.setExt4(iary.getExt4());
                }
                if (StringUtils.isNotEmpty(iary.getExt5())){
                    bis_data.setExt5(iary.getExt5());
                }
                if (StringUtils.isNotEmpty(iary.getExt6())){
                    bis_data.setExt6(iary.getExt6());
                }
                if (StringUtils.isNotEmpty(iary.getExt7())){
                    bis_data.setExt7(iary.getExt7());
                }
                if (StringUtils.isNotEmpty(iary.getExt8())){
                    bis_data.setExt8(iary.getExt8());
                }
                if (StringUtils.isNotEmpty(iary.getExt9())){
                    bis_data.setExt9(iary.getExt9());
                }
                if (StringUtils.isNotEmpty(iary.getExt10())){
                    bis_data.setExt10(iary.getExt10());
                }
                if (StringUtils.isNotEmpty(iary.getEvtUsr())){
                    bis_data.setEvtUsr(iary.getEvtUsr());
                }
                if (StringUtils.isNotEmpty(iary.getMailFlg())){
                    bis_data.setMailFlg(iary.getMailFlg());
                }
                bis_data.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
                dao.save(bis_data);
            }
        } catch (Exception ex) {
            logger.error(Util.stackTraceToString(ex));
            outTrx.setRtn_code("0000003");
            outTrx.setRtn_mesg(Util.stackTraceToString(ex));
            return outTrx;
        }
        outTrx.setOary(iaryList);
        outTrx.setTbl_cnt(iaryList.size());
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return outTrx;
    }


    @Transactional
    public XpbisdataO deleteProc(XpbisdataI inTrx) {
        XpbisdataO outTrx = new XpbisdataO();
        try {
            List<BisDataEntity> iaryList = inTrx.getIary();
            for (BisDataEntity iary : iaryList) {
                if (iary.getDataSeqId() != null) {
                    BisDataEntity bis_data = dao.get(BisDataEntity.class, iary.getDataSeqId());
                    if (bis_data != null) {
                        dao.delete(bis_data);
                    }
                }
            }
        } catch (Exception ex) {
            logger.error(Util.stackTraceToString(ex));
            outTrx.setRtn_code(E_XPLSTDAT_OTHER_ERROR + "");
            outTrx.setRtn_mesg(ex.toString());
            return outTrx;
        }
        outTrx.setRtn_code(RETURN_CODE_OK);
        outTrx.setRtn_mesg(RETURN_MESG_OK);
        return outTrx;
    }


    @Transactional
    public XpbisdataO updateProc(XpbisdataI inTrx) {
        XpbisdataO outTrx = new XpbisdataO();
        BisDataEntity iary = inTrx.getIary().get(0);
        String dataSeqId = iary.getDataSeqId();
        BisDataEntity bisDataEntity = dao.get(BisDataEntity.class, dataSeqId);
        if (StringUtils.isNotEmpty(iary.getDataCate())) bisDataEntity.setDataCate(iary.getDataCate());
        if (StringUtils.isNotEmpty(iary.getDataId())) bisDataEntity.setDataId(iary.getDataId());
        if (StringUtils.isNotEmpty(iary.getDataExt())) bisDataEntity.setDataExt(iary.getDataExt());
        if (StringUtils.isNotEmpty(iary.getDataItem())) bisDataEntity.setDataItem(iary.getDataItem());
        if (StringUtils.isNotEmpty(iary.getDataDesc())) bisDataEntity.setDataDesc(iary.getDataDesc());
        if (StringUtils.isNotEmpty(iary.getExt1())) bisDataEntity.setExt1(iary.getExt1());
        if (StringUtils.isNotEmpty(iary.getExt2())) bisDataEntity.setExt2(iary.getExt2());
        if (StringUtils.isNotEmpty(iary.getExt3())) bisDataEntity.setExt3(iary.getExt3());
        if (StringUtils.isNotEmpty(iary.getExt4())) bisDataEntity.setExt4(iary.getExt4());
        if (StringUtils.isNotEmpty(iary.getExt5())) bisDataEntity.setExt5(iary.getExt5());
        if (StringUtils.isNotEmpty(iary.getExt6())) bisDataEntity.setExt6(iary.getExt6());
        if (StringUtils.isNotEmpty(iary.getExt7())) bisDataEntity.setExt7(iary.getExt7());
        if (StringUtils.isNotEmpty(iary.getExt8())) bisDataEntity.setExt8(iary.getExt8());
        if (StringUtils.isNotEmpty(iary.getExt9())) bisDataEntity.setExt9(iary.getExt9());
        if (StringUtils.isNotEmpty(iary.getExt10())) bisDataEntity.setExt10(iary.getExt10());
        if (StringUtils.isNotEmpty(iary.getMailFlg())) bisDataEntity.setMailFlg(iary.getMailFlg());
        if (StringUtils.isNotEmpty(iary.getEvtUsr())) bisDataEntity.setEvtUsr(iary.getEvtUsr());
        bisDataEntity.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
        if(dataSeqId != null){
            dao.update(bisDataEntity);
            outTrx.setRtn_code(RETURN_CODE_OK);
            outTrx.setRtn_mesg(RETURN_MESG_OK);
        }
        return outTrx;
    }




    @Override
    @Transactional
    public String importExcel(MultipartFile file, String filename, HttpServletRequest request) throws IOException {
        String evtUser = request.getParameter("user");
        InputStream in;
        Workbook workbook;
        try {
            in = file.getInputStream();
            boolean is03Excel = filename.matches("^.+\\.(?i)(xls)$");
            //1、读取工作簿
            workbook = is03Excel ? new HSSFWorkbook(in) : new XSSFWorkbook(in);
        } catch (Exception e) {
            logger.error("error {}",e);
            throw new RuntimeException("文件类型异常");
        }
        //2、读取工作表
        String dataCate = request.getParameter("dataCate");
        Sheet sheet = workbook.getSheetAt(0);
        String hql = "from BisDataEntity where dataCate = 'CODE' and dataId = '" + dataCate + "'";
        List<BisDataEntity> dataList = dao.find(BisDataEntity.class,hql);

        String DATA_ID = "";
        String DATA_EXT = "";
        String DATA_ITEM = "";
        String DATA_DESC = "";
        String EXT_1 = "";
        String EXT_2 = "";
        String EXT_3 = "";
        String EXT_4 = "";
        String EXT_5 = "";
        String EXT_6 = "";
        String EXT_7 = "";
        String EXT_8 = "";
        String EXT_9 = "";
        String EXT_10 = "";

        Integer dataIdCell = null;
        Integer dataExtCell = null;
        Integer dataItemCell = null;
        Integer dataDescCell = null;
        Integer ext1Cell = null;
        Integer ext2Cell = null;
        Integer ext3Cell = null;
        Integer ext4Cell = null;
        Integer ext5Cell = null;
        Integer ext6Cell = null;
        Integer ext7Cell = null;
        Integer ext8Cell = null;
        Integer ext9Cell = null;
        Integer ext10Cell = null;

        // 查询出每一列对应的表字段
        for (int d = 0;d < dataList.size();d++){
            switch (dataList.get(d).getDataExt()){
                case "DATA_ID": DATA_ID = dataList.get(d).getDataDesc(); break;
                case "DATA_EXT": DATA_EXT = dataList.get(d).getDataDesc(); break;
                case "DATA_ITEM": DATA_ITEM = dataList.get(d).getDataDesc(); break;
                case "DATA_DESC": DATA_DESC = dataList.get(d).getDataDesc(); break;
                case "EXT_1": EXT_1 = dataList.get(d).getDataDesc(); break;
                case "EXT_2": EXT_2 = dataList.get(d).getDataDesc(); break;
                case "EXT_3": EXT_3 = dataList.get(d).getDataDesc(); break;
                case "EXT_4": EXT_4 = dataList.get(d).getDataDesc(); break;
                case "EXT_5": EXT_5 = dataList.get(d).getDataDesc(); break;
                case "EXT_6":  EXT_6 = dataList.get(d).getDataDesc(); break;
                case "EXT_7": EXT_7 = dataList.get(d).getDataDesc(); break;
                case "EXT_8": EXT_8 = dataList.get(d).getDataDesc(); break;
                case "EXT_9": EXT_9 = dataList.get(d).getDataDesc(); break;
                case "EXT_10": EXT_10 = dataList.get(d).getDataDesc(); break;
            }
        }
        for (int col = 0; col < sheet.getRow(0).getPhysicalNumberOfCells(); col++){
            String cell_title = sheet.getRow(0).getCell(col).getStringCellValue();
            if (StringUtils.isNotEmpty(DATA_ID) && DATA_ID.equals(cell_title)) dataIdCell = col;
            if (StringUtils.isNotEmpty(DATA_EXT) && DATA_EXT.equals(cell_title)) dataExtCell = col;
            if (StringUtils.isNotEmpty(DATA_ITEM) && DATA_ITEM.equals(cell_title)) dataItemCell = col;
            if (StringUtils.isNotEmpty(DATA_DESC) && DATA_DESC.equals(cell_title)) dataDescCell = col;
            if (StringUtils.isNotEmpty(EXT_1) && EXT_1.equals(cell_title)) ext1Cell = col;
            if (StringUtils.isNotEmpty(EXT_2) && EXT_2.equals(cell_title)) ext2Cell = col;
            if (StringUtils.isNotEmpty(EXT_3) && EXT_3.equals(cell_title)) ext3Cell = col;
            if (StringUtils.isNotEmpty(EXT_4) && EXT_4.equals(cell_title)) ext4Cell = col;
            if (StringUtils.isNotEmpty(EXT_5) && EXT_5.equals(cell_title)) ext5Cell = col;
            if (StringUtils.isNotEmpty(EXT_6) && EXT_6.equals(cell_title)) ext6Cell = col;
            if (StringUtils.isNotEmpty(EXT_7) && EXT_7.equals(cell_title)) ext7Cell = col;
            if (StringUtils.isNotEmpty(EXT_8) && EXT_8.equals(cell_title)) ext8Cell = col;
            if (StringUtils.isNotEmpty(EXT_9) && EXT_9.equals(cell_title)) ext9Cell = col;
            if (StringUtils.isNotEmpty(EXT_10) && EXT_10.equals(cell_title)) ext10Cell = col;
        }

        try {
            if (sheet.getPhysicalNumberOfRows() > 1) {
                for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                    Row row = sheet.getRow(i);
                    boolean isNull = true;

                    BisDataEntity mBisDataD = new BisDataEntity();
                    mBisDataD.setDataSeqId(UUID.randomUUID().toString().replace("-", "").substring(0, 25));
                    mBisDataD.setEvtTimestamp(new Timestamp(System.currentTimeMillis()));
                    mBisDataD.setEvtUsr(evtUser);
                    mBisDataD.setDataCate(dataCate);
                    if (dataIdCell != null) { mBisDataD.setDataId(getCellValue(row.getCell(dataIdCell))); isNull = false; }
                    if (dataExtCell != null) { mBisDataD.setDataExt(getCellValue(row.getCell(dataExtCell))); isNull = false; }
                    if (dataItemCell != null) { mBisDataD.setDataItem(getCellValue(row.getCell(dataItemCell))); isNull = false; }
                    if (dataDescCell != null) { mBisDataD.setDataDesc(getCellValue(row.getCell(dataDescCell))); isNull = false; }
                    if (ext1Cell != null) { mBisDataD.setExt1(getCellValue(row.getCell(ext1Cell))); isNull = false; }
                    if (ext2Cell != null) { mBisDataD.setExt2(getCellValue(row.getCell(ext2Cell))); isNull = false; }
                    if (ext3Cell != null) { mBisDataD.setExt3(getCellValue(row.getCell(ext3Cell))); isNull = false; }
                    if (ext4Cell != null) { mBisDataD.setExt4(getCellValue(row.getCell(ext4Cell))); isNull = false; }
                    if (ext5Cell != null) { mBisDataD.setExt5(getCellValue(row.getCell(ext5Cell))); isNull = false; }
                    if (ext6Cell != null) { mBisDataD.setExt6(getCellValue(row.getCell(ext6Cell))); isNull = false; }
                    if (ext7Cell != null) { mBisDataD.setExt7(getCellValue(row.getCell(ext7Cell))); isNull = false; }
                    if (ext8Cell != null) { mBisDataD.setExt8(getCellValue(row.getCell(ext8Cell))); isNull = false; }
                    if (ext9Cell != null) { mBisDataD.setExt9(getCellValue(row.getCell(ext9Cell))); isNull = false; }
                    if (ext10Cell != null) { mBisDataD.setExt10(getCellValue(row.getCell(ext10Cell))); isNull = false; }
                    if (isNull) throw  new RuntimeException("数据不能为空");
                    dao.save(mBisDataD);
                }
            }
        }catch (RuntimeException e1){
            e1.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return e1.getMessage();
        }catch (Exception e){
            e.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return "[导入失败] 文件异常，请确认文件内容是否符合模板要求!";
        }
        return "success";
    }


    private String getCellData(Cell cell) {
        //if (cell == null) throw  new RuntimeException("有单元格数据为空");
        if (cell == null) return null;
        cell.setCellType(cell.CELL_TYPE_STRING);
        return cell.getStringCellValue();
    }

}

